﻿<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="发货供货明细.aspx.cs" Inherits="syc.ZhiJian.Report.发货供货明细" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">
    <title></title>
    <script type="text/javascript" language="javascript" src="/common.js"></script>

</head>
<body>
    <form id="form1" runat="server">
   <div class="hooo">
        <div id="wintop" class="wintop"><span lang="zh-cn"><u>发货供货明细</u></span></div>

        <asp:Panel ID="Panel1" runat="server" CssClass="panel" Height="60px">
  
        <table>
        <tr id="row2" runat="server">
        <td>起始日期：</td>
        <td>
            <asp:TextBox ID="Date1" runat="server" Width="100px" AutoPostBack="true">
            </asp:TextBox>
            <img alt="" src="/Icons/29.png" align="middle" onclick="makecalendar(document.getElementById('Date1').value, document.getElementById('Date1'));" />
        </td>        
        <td>截止日期：</td>
        <td>
            <asp:TextBox ID="Date2" runat="server" Width="100px" AutoPostBack="true">
            </asp:TextBox> 
            <img alt="" src="/Icons/29.png" align="middle" onclick="makecalendar(document.getElementById('Date2').value, document.getElementById('Date2'));" />
        </td>
        <td>
            <input type="button" class="smallbutton" id="clear" value="清空日期"/>
        </td>
       </tr>
       
       <tr>
        <td>产品：</td>
        <td><asp:SqlDataSource ID="DS_ChanPin" runat="server" 
    ConnectionString="<%$ ConnectionStrings:SCMConnectionString1 %>" 
    SelectCommand="SELECT [ID],[品名]+' '+[钢级]+' '+[规格] AS 名称 FROM [产品]"></asp:SqlDataSource>
        <asp:DropDownList ID="DList_Produce" runat="server" AutoPostBack="True" Width="150px" 
                Font-Size="12px" DataSourceID="DS_ChanPin" DataTextField="名称" DataValueField="ID">
        </asp:DropDownList>
        </td>
        <td>
        发往单位：
        </td>
        <td>
       <asp:SqlDataSource ID="SqlDataSource2" runat="server" 
                ConnectionString="<%$ ConnectionStrings:SCMConnectionString1 %>" 
                SelectCommand="SELECT [ID], [名称] FROM [产品订货单位]"></asp:SqlDataSource>
        <asp:DropDownList ID="DanWei" runat="server"  
                DataSourceID="SqlDataSource2" DataTextField="名称" DataValueField="ID"
        AutoPostBack="True">
        </asp:DropDownList>

        
        </td>
        <td><asp:Button ID="Export" runat="server" CssClass="button" Text="导出" onclick="Export_Click" /></td>
        </tr>
        </table>

        </asp:Panel>

        <asp:Panel ID="Panel2" runat="server" CssClass="panel2">
        <asp:Label ID="Message" runat="server" Text="·准备就绪" CssClass="prompt" Visible="True"></asp:Label>
        </asp:Panel> 

                <div align="center" id="report"">    
                <h1 style="text-align: center;">
            <%=Date1.Text%> ~ <%=Date2.Text%><%=DanWei.SelectedItem.Text%><%=DList_Produce.SelectedItem.Text%>发货供货明细</h1>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" 
            DataSourceID="SqlDataSource1" style="text-align: center" BorderColor="Black" 
                BorderStyle="Solid" BorderWidth="2px">
            <RowStyle Height="40px" />
            <Columns>
                <asp:BoundField DataField="出库日期" HeaderText="出库日期" 
                    SortExpression="出库日期" DataFormatString="{0:yyyy-MM-dd}" >
                </asp:BoundField>
                <asp:BoundField DataField="名称" HeaderText="供货商" 
                    SortExpression="名称" ItemStyle-Width="100" />
                <asp:BoundField DataField="捆数" HeaderText="捆数" 
                    SortExpression="捆数" ReadOnly="True" ItemStyle-Width="50">
                </asp:BoundField>
                <asp:BoundField DataField="支数" HeaderText="支数" 
                    SortExpression="支数" ReadOnly="True" ItemStyle-Width="50">
                </asp:BoundField>
                <asp:BoundField DataField="重量" HeaderText="重量(吨)" 
                    SortExpression="重量" ReadOnly="True" ItemStyle-Width="80" DataFormatString="{0:F3}">
                </asp:BoundField>
                <asp:BoundField DataField="长度" HeaderText="长度" ReadOnly="True" 
                    SortExpression="长度" ItemStyle-Width="80">
                </asp:BoundField>
                <asp:BoundField DataField="生产分厂" HeaderText="生产分厂" ReadOnly="True" 
                    SortExpression="生产分厂" ItemStyle-Width="80" >
                </asp:BoundField>
                <asp:BoundField DataField="车数" HeaderText="车数" ReadOnly="True" 
                    SortExpression="车数" ItemStyle-Width="80" >
                </asp:BoundField>
                <asp:BoundField DataField="生产月份" HeaderText="生产月份(非精确)" ReadOnly="True" 
                    SortExpression="生产月份" ItemStyle-Width="200" >
                </asp:BoundField>
                <asp:BoundField DataField="入库单序号" HeaderText="入库单序号(非精确)" ReadOnly="True" 
                    SortExpression="入库单序号" ItemStyle-Width="300" >
                </asp:BoundField>                
            </Columns>
            <HeaderStyle Height="40px" />
        </asp:GridView>
        </div>
    
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" 
            ConnectionString="<%$ ConnectionStrings:SCMConnectionString1 %>" 
            SelectCommand="
 SELECT convert(nvarchar(10),kc.出库日期,120) AS 出库日期, 
	   gh.名称, 
	   COUNT(*) AS 捆数, 
	   SUM(kc.支数) AS 支数, 
	   SUM(kc.重量)/1000 AS 重量, 
	   SUM(kc.长度) AS 长度,
	   CASE WHEN kc.生产线 between 100 and 199 then '一分厂'
			WHEN kc.生产线 = 201 then '二分厂#1'
			WHEN kc.生产线 = 202 then '二分厂#2'
			WHEN kc.生产线 between 300 and 399 then '三分厂'
			WHEN kc.生产线 between 400 and 499 then '四分厂'
			WHEN kc.生产线 = 501 then '五分厂#1'
			WHEN kc.生产线 = 502 then '五分厂#2'
	   END AS 生产分厂,
	   COUNT(distinct kc.出库单ID) as 车数,
       (select distinct CAST(MONTH(kc2.入库日期) as CHAR(2))+'月'
       from 产品库存 as kc2
       WHERE kc2.出库日期=kc.出库日期 and 
             kc2.生产线=kc.生产线 and
             kc2.状态=2 and
             kc2.产品类别ID=@产品 and
             kc2.原料供货ID=kc.原料供货ID
       for XML path('')       
       ) as 生产月份,
       (select distinct CAST(rk.序号 as varchar(4))+'号'
       from 产品库存 as kc2 inner join
            产品入库单 as rk on kc2.入库单ID=rk.ID
       WHERE kc2.出库日期=kc.出库日期 and 
             kc2.生产线=kc.生产线 and
             kc2.状态=2 and
             kc2.产品类别ID=@产品 and
             kc2.原料供货ID=kc.原料供货ID
       for XML path('')       
       ) as 入库单序号
FROM dbo.产品库存 AS kc INNER JOIN
     dbo.产品出库单 AS ck ON kc.出库单ID = ck.ID INNER JOIN
     dbo.原料供货单位 AS gh ON kc.原料供货ID=gh.ID
WHERE (kc.产品类别ID = @产品) AND
      ((kc.出库日期) between @起始日期 and @截止日期) AND
      ck.订货单位ID=@订货单位 AND
      (kc.状态=2)
GROUP BY kc.出库日期,gh.名称,kc.原料供货ID,kc.生产线
order by kc.出库日期,kc.生产线">
<SelectParameters>
    <asp:ControlParameter ControlID="DList_Produce" PropertyName="SelectedValue" Name="产品" DefaultValue="1" DbType="Int32"/>
    <asp:ControlParameter ControlID="DanWei" PropertyName="SelectedValue" Name="订货单位" DbType="Int32"/>
    <asp:ControlParameter ControlID="Date1" PropertyName="Text" Name="起始日期" DbType="Date" />
       <asp:ControlParameter ControlID="Date2" PropertyName="Text" Name="截止日期" DbType="Date" />
</SelectParameters>
</asp:SqlDataSource>
    
    </div>
    </form>
</body>
</html>
